﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using HK2009.Common;

public class TimeTask
{
    DataBase db = new DataBase();

    public void TimeTaskSendEmail()
    {
        //提取需要发送的distinct email集合

        SqlParameter[] parms = new SqlParameter[1];
        parms[0] = new SqlParameter("@Status", "0");
        string sqlStr = @"select distinct SR.* from Sy_Recipient as SR inner join Sy_PendingMail as SPM on SR.PendingMailID=SPM.PKID
                              where SR.Status=@Status ";
        DataTable dt = db.RunSqlTab(sqlStr, parms);
        if (dt != null && dt.Rows.Count > 0)
        {
            SqlParameter[] parmsE = new SqlParameter[2];
            DataTable dtC = new DataTable();
            StringBuilder strB = new StringBuilder();

            for (int i = 0; i < dt.Rows.Count; i++)//循环Email集合,把需要发到该email下的邮件内容合起来,并发送.
            {
                if (StringUtil.IsEmailAddress(dt.Rows[i]["Email"].ToString()))
                {
                    parmsE[0] = new SqlParameter("@Status", "0");
                    parmsE[1] = new SqlParameter("@Email", dt.Rows[i]["Email"].ToString());
                    sqlStr = @"select SPM.*,SR.Status,SR.Email from Sy_PendingMail as SPM inner join Sy_Recipient as SR on SR.PendingMailID=SPM.PKID
                                    where Status=@Status and Email=@Email ";
                    dtC = db.RunSqlTab(sqlStr, parmsE);

                    if (dtC != null && dtC.Rows.Count > 0)//把发到同一email下的Email内容集合起来
                    {
                        string title = @"Hello, [title]: 


some reminds from special-line,

";
                        title=title.Replace("[title]", dt.Rows[i]["Email"].ToString());
                        strB.Append(title);
                        for (int j = 0; j < dtC.Rows.Count; j++)
                        {
                            strB.Append(dtC.Rows[j]["App"].ToString());
                           // strB.Append(@"\r\n");
                            strB.Append(dtC.Rows[j]["Content"].ToString());
                        }

                        //发送
                        string subject = "reminds from special-line!";
                        
                        SendMail send = new SendMail();
                        string content = strB.ToString();
                        send.MailBody = content;
                        send.MailSubject = subject;
                        send.MailTo = dt.Rows[i]["Email"].ToString();
                        try
                        {
                             send.Send();

                            //发送成功后修改状态
                            SqlParameter[] parmsU = new SqlParameter[3];
                            parmsU[0] = new SqlParameter("@Status", "1");
                            parmsU[1] = new SqlParameter("@Email", dt.Rows[i]["Email"].ToString());
                            parmsU[2] = new SqlParameter("@SendTime", DateTime.Now);
                            sqlStr = "update Sy_Recipient set Status=@Status,SendTime=@SendTime where Email=@Email";
                            db.ExecSqlTransaction(sqlStr, parmsU);
                        }
                        catch
                        {

                        }
                        finally
                        {
                            strB.Remove(0, strB.ToString().Length);
                        }
                    }
                }
            }
        }
    }

    /// <summary>
    /// 添加发送Email的相关内容到数据库
    /// </summary>
    /// <param name="app">应用编号</param>
    /// <param name="content">Email内容</param>
    /// <param name="emailList">要发送的Email集合</param>
    /// <param name="userID">userID</param>
    public void InsertEmailContent(string app,string content,List<string> emailList,int userID)
    {
        List<SqlParameter> lst = new List<SqlParameter>();
        SqlParameter[] parms = null;
        string sqlStr = string.Empty;

        lst.Add(new SqlParameter("@App",app));
        lst.Add(new SqlParameter("@Content",content));
        lst.Add(new SqlParameter("@AddTime", DateTime.Now));

        lst.Add(new SqlParameter("@Creater", userID.ToString()));
        SqlParameter parmR = new SqlParameter("@ReturnValues", 0);
        parmR.Direction = ParameterDirection.Output;
        parmR.SqlDbType = SqlDbType.Int;
        lst.Add(parmR);

        sqlStr += "INSERT INTO Sy_PendingMail(App,AddTime,Creater,Content) VALUES(@App,@AddTime,@Creater,@Content) set @ReturnValues=@@Identity;";

        if (emailList != null && emailList.Count > 0)
        {
            for (int i = 0; i < emailList.Count; i++)
            {
                lst.Add(new SqlParameter("@Status" + i.ToString(), "0"));
                lst.Add(new SqlParameter("@SendTime" + i.ToString(), System.DBNull.Value));
                lst.Add(new SqlParameter("@Email" + i.ToString(), emailList[i]));

                sqlStr += "INSERT INTO Sy_Recipient(PendingMailID,Email,Status,SendTime) VALUES(@ReturnValues,@Email" + i.ToString() + ",@Status" + i.ToString() + ",@SendTime" + i.ToString() + ");";

            }
        }

        parms = lst.ToArray();
        try
        {
            db.ExecSqlTransaction(sqlStr, parms);
        }
        catch
        { 
            
        }


    }

    /// <summary>
    /// 添加发送Email的相关内容到数据库
    /// </summary>
    /// <param name="app">应用编号</param>
    /// <param name="content">Email内容</param>
    /// <param name="emailList">要发送的Email集合</param>
    /// <param name="userID">userID</param>
    public void InsertEmailContent(string app, string content, string[] emailList, int userID)
    {
        List<SqlParameter> lst = new List<SqlParameter>();
        SqlParameter[] parms = null;
        string sqlStr = string.Empty;

        lst.Add(new SqlParameter("@App", app));
        lst.Add(new SqlParameter("@Content", content));
        lst.Add(new SqlParameter("@AddTime", DateTime.Now));

        lst.Add(new SqlParameter("@Creater", userID.ToString()));
        SqlParameter parmR = new SqlParameter("@ReturnValues", 0);
        parmR.Direction = ParameterDirection.Output;
        parmR.SqlDbType = SqlDbType.Int;
        lst.Add(parmR);

        sqlStr += "INSERT INTO Sy_PendingMail(App,AddTime,Creater,Content) VALUES(@App,@AddTime,@Creater,@Content) set @ReturnValues=@@Identity;";

        if (emailList != null && emailList.Length > 0)
        {
            for (int i = 0; i < emailList.Length; i++)
            {
                lst.Add(new SqlParameter("@Status" + i.ToString(), "0"));
                lst.Add(new SqlParameter("@SendTime" + i.ToString(), System.DBNull.Value));
                lst.Add(new SqlParameter("@Email" + i.ToString(), emailList[i]));

                sqlStr += "INSERT INTO Sy_Recipient(PendingMailID,Email,Status,SendTime) VALUES(@ReturnValues,@Email" + i.ToString() + ",@Status" + i.ToString() + ",@SendTime" + i.ToString() + ");";

            }
        }

        parms = lst.ToArray();
        try
        {
            db.ExecSqlTransaction(sqlStr, parms);
        }
        catch
        {

        }

    }

}

